/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package dao;

import db.DBAccess;
import entity.Student;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author DucND
 */
public class StudentDAO {

    public static List<Student> getStudentList(String keyword, String SearchType) {
        List<Student> contractList = new ArrayList<Student>();
        try {
            Connection conn = DBAccess.getConnection();
            ResultSet rs = null;
            if (keyword == null || keyword.trim().length()==0 ) {
                PreparedStatement prst = conn.prepareStatement("Select * from Student");
                rs = prst.executeQuery();
            } else {
                PreparedStatement prst = conn.prepareStatement("Select * from Student where "+SearchType.trim()+" like '%"+keyword.trim()+"%'");
                rs = prst.executeQuery();
            }
            while (rs != null && rs.next()) {
                Student st = new Student();
                st.setStudentID(rs.getString("StudentID"));
                st.setName(rs.getString("Name"));
                st.setAge(rs.getInt("Age"));
                st.setAddress(rs.getString("Address"));
                st.setGender(rs.getString("Gender").charAt(0));
                st.setBatch(rs.getString("Batch"));
                st.setImage(rs.getString("Image"));

                contractList.add(st);
            }
        } catch (SQLException ex) {
            Logger.getLogger(StudentDAO.class.getName()).log(Level.SEVERE, null, ex);
        }

        return contractList;
    }
    
    public static void AddStudent(Student st) {
        try {
            Connection conn = DBAccess.getConnection();

            PreparedStatement prst = conn.prepareStatement("insert into Student(StudentID,Name,Age,Address,Gender,Batch,Image) values (?,?,?,?,?,?,?)");
            prst.setString(1, st.getStudentID().toUpperCase());
            prst.setString(2, st.getName());
            prst.setInt(3, st.getAge());
            prst.setString(4, st.getAddress());
            prst.setString(5, String.valueOf(st.getGender()));
            prst.setString(6, st.getBatch());
            prst.setString(7, st.getImage());
            prst.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(StudentDAO.class.getName()).log(Level.SEVERE, null, ex);
        }

    }

    public static void DeleteStudent(Student del) {
        try {
            Connection conn = DBAccess.getConnection();
            PreparedStatement prst = conn.prepareStatement("Delete from Student\n" + " WHERE StudentID =?");
            prst.setString(1, del.getStudentID());
            prst.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(StudentDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public static void delete(String id) {
        try {
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            Connection con
                    = DriverManager.getConnection("jdbc:odbc:SMS");
            PreparedStatement prst
                    = con.prepareStatement("Delete from Student where StudentID = '" + id + "'");
            prst.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(StudentDAO.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(StudentDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public static void edit(Student stu) {
        try {
            Connection con = DBAccess.getConnection();
            PreparedStatement prst
                    = con.prepareStatement("Update Student set Name = ?, Age = ?, Address = ?, Gender = ?, Batch = ?, Image = ? where StudentId = ?");
            prst.setString(1, stu.getName());
            prst.setInt(2, stu.getAge());
            prst.setString(3, stu.getAddress());
            prst.setString(4, String.valueOf(stu.getGender()));
            prst.setString(5, stu.getBatch());
            prst.setString(6, stu.getImage());
            prst.setString(7, stu.getStudentID());
            prst.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(StudentDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public static Student getStudent(String Id) {
        Student stu = new Student();

        try {
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            Connection con = DriverManager.getConnection("jdbc:odbc:SMS");
            PreparedStatement prst
                    = con.prepareStatement("select * from Student where StudentID = '" + Id + "'");
            ResultSet rs = prst.executeQuery();
            while (rs != null && rs.next()) {
                stu.setStudentID(Id);
                stu.setName(rs.getString("Name"));
                stu.setAge(rs.getInt("Age"));
                stu.setAddress(rs.getString("Address"));
                stu.setGender(rs.getString("Gender").charAt(0));
                stu.setBatch(rs.getString("Batch"));
                stu.setImage(rs.getString("Image"));
            }
        } catch (SQLException ex) {
            Logger.getLogger(StudentDAO.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(StudentDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return stu;
    }
}
